package com.hns.can.master.component;

import com.hns.can.master.entity.BaseCarInfoEntity;
import com.hns.can.master.entity.BaseStnInfoEntity;
import com.hns.frame.enumrate.MsgType;
import com.hns.frame.exception.BusinessException;
import com.hns.tool.pub.PubUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @版权：HNS 版权所有 (c) 2013
 * @author:JeffXu
 * @version Revision 1.0.0
 * @email:835264047@qq.com
 * @see:
 * @创建日期：2017-4-5
 * @功能说明：解析车辆信息excel文件成list对象
 * @begin
 * @修改记录:
 * @修改后版本          修改人      	修改内容
 * @2014-8-14  	         chenl        	创建
 * @end
 */
public class ParseCarInfoExcel {
    
    /**
     * @param file
     * @return
     * @throws IOException
     * @author:JeffXu
     * @email:835264047@qq.com
     * @创建日期:2014-8-14
     * @功能说明：提供对外访问的方法
     */
    public static List<BaseCarInfoEntity> parseExcelToList(File file) throws IOException {
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) { // 读取 office 2003 excel
            wb = new HSSFWorkbook(new FileInputStream(file));
        } else if ("xlsx".equals(extension)) { // 读取Office 2007 excel
            wb = new XSSFWorkbook(new FileInputStream(file));
        } else {
            file.delete();
            throw new BusinessException(MsgType.ERROR.getFlag(), "不支持的文件类型");
        }
        
        sheet = wb.getSheetAt(0);
//        if (!"站点经纬度信息".equals(sheet.getSheetName())) {
//            file.delete();
//            throw new BusinessException(MsgType.ERROR.getFlag(), "您导入的模版有误。");
//        }
        
        List<BaseCarInfoEntity> list = new ArrayList<BaseCarInfoEntity>();
        Map<Integer,String> map = new HashMap<Integer,String>();
        int begin = sheet.getFirstRowNum();
        int end = sheet.getLastRowNum();
        for (int i = 4; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            System.out.print(row);
            if (row == null) {
                continue;
            }
            row = sheet.getRow(i);
            BaseCarInfoEntity entity = new BaseCarInfoEntity();
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            for(int j = 0;j<cells;j++){
                cell = row.getCell(j);
                String cellValue = getCellValue(cell).trim();
                if( i == 4){
                    switch (j) {
                        case 0:
                            map.put(0,cellValue);
//                            System.out.print(cellValue);
                            break;
                        case 1:
                            map.put(1,cellValue);
//                            System.out.print(cellValue);
                            break;
                        case 2:
                            map.put(2,cellValue);
//                            System.out.print(cellValue);
                            break;
                        case 3:
                            map.put(3,cellValue);
//                            System.out.print(cellValue);
                            break;
                        case 4:
                            map.put(4,cellValue);
//                            System.out.print(cellValue);
                            break;
                        case 5:
                            map.put(5,cellValue);
//                            System.out.print(cellValue);
                            break;
                        case 6:
                            map.put(6,cellValue);
//                            System.out.print(cellValue);
                            break;
                        case 7:
                            map.put(7,cellValue);
//                            System.out.print(cellValue);
                            break;
                        case 8:
                            map.put(8,cellValue);
//                            System.out.print(cellValue);
                            break;
                        case 9:
                            map.put(9,cellValue);
//                            System.out.print(cellValue);
                            break;
                    }
                }else {
                    switch (map.get(j)) {
                        case "车牌号\n" +
                                "例:浙F26867":
                            entity.setLicPltNo(cellValue);
//                            System.out.print("车牌号:"+cellValue);
                            break;
                        case "车辆编号\n" +
                                "例:123":
                            entity.setCarInCd(cellValue);
//                            System.out.print("车辆编号:"+cellValue);
                            break;
                        case "车辆类型\n" +
                                "例:CNG(新能源)":
                            switch (cellValue){
                                case "柴油车(传统)":
                                    entity.setCarType("CAT00001");
                                    break;
                                case "汽油车(传统)":
                                    entity.setCarType("CAT00002");
                                    break;
                                case "混合动力车(新能源)":
                                    entity.setCarType("CAT00003");
                                    break;
                                case "纯电动车(新能源)":
                                    entity.setCarType("CAT00004");
                                    break;
                                case "CNG(新能源)":
                                    entity.setCarType("CAT00005");
                                    break;
                                case "LNG":
                                    entity.setCarType("CAT00006");
                                    break;
                                case "纯电动(双路BMS)":
                                    entity.setCarType("CAT00007");
                                    break;
                                case "纯电动2":
                                    entity.setCarType("CAT00008");
                                    break;
                            }
//                            System.out.print("车辆类型:"+cellValue);
                            break;
                        case "燃油类型\n" +
                                "例:0#-柴油":
                            switch (cellValue){
                                case "0#-柴油":
                                    entity.setFlOilType("FUT00004");
                                    break;
                                case "90#-汽油":
                                    entity.setFlOilType("FUT00001");
                                    break;
                                case "93#-汽油":
                                    entity.setFlOilType("FUT00002");
                                    break;
                                case "97#-汽油":
                                    entity.setFlOilType("FUT00003");
                                    break;
                                case "CNG":
                                    entity.setFlOilType("FUT00005");
                                    break;
                                case "LNG":
                                    entity.setFlOilType("FUT00008");
                                    break;
                                case "电能":
                                    entity.setFlOilType("FUT00007");
                                    break;
                                case "其它":
                                    entity.setFlOilType("FUT00006");
                                    break;
                            }
//                            System.out.print("燃油类型:"+cellValue);
                            break;
                        case "车辆型号 \n" +
                                "例:HZK6850BEVH":
                            entity.setCarModel(cellValue);
//                            System.out.print("车辆型号:"+cellValue);
                            break;
                        case "生产厂家\n" +
                                "例:某某某汽车制造商":
                            entity.setProduceFacty(cellValue);
//                            System.out.print("生产厂家:"+cellValue);
                            break;
                        case "购置日期\n" +
                                "例:2017-04-05":
                            entity.setPrchDate(cellValue);
//                            System.out.print("购置日期:"+cellValue);
                            break;
                        case "车架号\n" +
                                "例:LL3AHCMG7FA013068":
                            entity.setFrameNo(cellValue);
//                            System.out.print("车架号:"+cellValue);
                            break;
                        case "发动机厂家\n" +
                                "例:上柴":
                            entity.setEngiFacty(cellValue);
//                            System.out.print("发动机厂家:"+cellValue);
                            break;
                        case "发动机型号\n" +
                                "例:SC8DT250Q5":
                            entity.setEngiModel(cellValue);
//                            System.out.print("发动机型号:"+cellValue);
                            break;
                    }
                }
            }
            if(i!=4){
                if(PubUtil.isEmpty(entity.getCarModel()) && PubUtil.isEmpty(entity.getLicPltNo()) && PubUtil.isEmpty(entity.getCarInCd())
                && PubUtil.isEmpty(entity.getCarType()) && PubUtil.isEmpty(entity.getCarModel()) && PubUtil.isEmpty(entity.getFlOilType()) ){
                    continue;
                }
                list.add(entity);
            }
        }
        return list;
    }
    
    /**
     * @param cell
     * @return
     * @author:JeffXu
     * @email:835264047@qq.com
     * @创建日期:2014-8-14
     * @功能说明：得到每个表格的值
     */
    private static String getCellValue(Cell cell) {
        String value = null;
        DecimalFormat df = new DecimalFormat("0");// 格式化 number String // 字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("###.######");// 格式化数字
        if(cell != null){
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        value = nf.format(cell.getNumericCellValue());
                    } else {
//                        value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                        Double d = cell.getNumericCellValue();

                        value = nf.format(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    value = cell.toString();
            }
        }else{
            value = "";
        }
        return value;
    }
    
    public static boolean isNum(String cellValue){
    	if(cellValue.indexOf(".") == -1){
    		return false;
    	}
    	String a = cellValue.substring(cellValue.indexOf(".")+1,cellValue.length());
    	if(a.length()!=6){
    		return false;
    	}
    	return true;
    }
    
//    public static void main(String[] args) throws IOException {
//        File file = new File("x:\\statLgtLtt.xls");
//        List<BaseStnInfoEntity> list = parseExcelToList(file);
//        System.out.println(list.size());
//        System.out.println(list);
//    }
}
